package com.orange;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Util;

import orange.database.DBIns;

public class Owtradeconn extends BaseAction {
	public Owtradeconn(String[] str1, String[] str2, String[] str3,
			String[] str4) throws SQLException {
		super();
		String sql;
		ResultSet rs;
		Connection conn ;
		sql = change2_2(str1, str2, str3, str4);
		System.out.println(sql);
		conn = DBIns.getInstance().getConnection();
		Statement stmt = conn.createStatement();
		rs =stmt.executeQuery(sql);
		value = combine2_2(rs);
		DBIns.getInstance().release(conn);
	}

	// str1 年份 str2出口国家 str3进口国家 str4海关编号
	public String change2_2(String[] str1, String[] str2, String[] str3,
			String[] str4) {
		try {

			nianfen = Util.Convert(str1);

			int m = str2.length;
			chuguojia = Util.Convert(str2);

			int p = str3.length;
			jinguojia = Util.Convert(str3);

			bianhao = Util.Convert(str4);

			 String cchuguojia = new String(str2[m - 1].trim().getBytes(
			 "ISO-8859-1"), "utf-8");
			
			 String jjinguojia = new String(str3[p - 1].trim().getBytes(
			 "ISO-8859-1"), "utf-8");
//			String cchuguojia = new String(str2[m - 1].trim().getBytes());
//
//			String jjinguojia = new String(str3[p - 1].trim().getBytes());

			String sql = "1";
			if (jjinguojia.equals("all")) {
				if (cchuguojia.equals("all"))
					sql = "select NianFen,BianHao,Chu_GuoJia,Jin_GuoJia,JinE,ZhongLiang from orang_w_jinchumaoyi where orang_w_jinchumaoyi.NianFen in ("
							+ nianfen
							+ ") and orang_w_jinchumaoyi.BianHao in ("
							+ bianhao + ") order by NianFen";
				else
					sql = "select NianFen,BianHao,Chu_GuoJia,Jin_GuoJia,JinE,ZhongLiang from orang_w_jinchumaoyi where  orang_w_jinchumaoyi.NianFen in ("
							+ nianfen
							+ ") and orang_w_jinchumaoyi.BianHao in ("
							+ bianhao
							+ ") and orang_w_jinchumaoyi.Chu_GuoJia in ("
							+ chuguojia + ") order by NianFen";
			} else {
				if (cchuguojia.equals("all"))
					sql = "select NianFen,BianHao,Chu_GuoJia,Jin_GuoJia,JinE,ZhongLiang from orang_w_jinchumaoyi where  orang_w_jinchumaoyi.NianFen in ("
							+ nianfen
							+ ") and orang_w_jinchumaoyi.BianHao in ("
							+ bianhao
							+ ") and orang_w_jinchumaoyi.Jin_GuoJia in ("
							+ jinguojia + ") order by NianFen";
				else
					sql = "select NianFen,BianHao,Chu_GuoJia,Jin_GuoJia,JinE,ZhongLiang from orang_w_jinchumaoyi where  orang_w_jinchumaoyi.NianFen in ("
							+ nianfen
							+ ") and orang_w_jinchumaoyi.BianHao in ("
							+ bianhao
							+ ") and orang_w_jinchumaoyi.Chu_GuoJia in ("
							+ chuguojia
							+ ") and orang_w_jinchumaoyi.Jin_GuoJia in ("
							+ jinguojia + ")  order by NianFen";
			}
			return sql;
		} catch (Exception e) {
			return "error";
		}
	}

	public String combine2_2(ResultSet rs1) {
//		Beforefirst(rs1);
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份(Year)</td><td>编码(Code)</td><td>出口国家(Exporting countries)</td><td>进口国家(Importing countries)</td><td>贸易金额(Trade Value)$</td><td>贸易量(NetWeight)Kg</td></tr></thead>");
		try {
			while (rs1.next()) {
				buffer1.append("<tr><td>" + rs1.getObject("NianFen")
						+ "</td><td>" + rs1.getObject("BianHao") + "</td><td>"
						+ rs1.getObject("Chu_GuoJia") + "</td><td>"
						+ rs1.getObject("Jin_GuoJia") + "</td><td>"
						+ rs1.getString("JinE") + "</td><td>"
						+ rs1.getString("ZhongLiang") + "</td></tr>");
			}
			value = new String(buffer1);
			return value;
		} catch (Exception e) {
			return "-1";
		}
	}

	public static void main(String[] arg) {
		try {
			String[] str1 = { "2000" }, str2 = { "china" }, str3 = { "all" }, str4 = { "200911" };
			Owtradeconn ow = new Owtradeconn(str1, str2, str3, str4);
			System.out.println(ow.getValue());
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
}
